import time 

import numpy 

import pymysql 

import pandas as pd 

from datetime import datetime 

from pymysql.cursors import Cursor, DictCursor 

class MysqlUtils(object): 

def __init__(self, *args): 

self.conn = pymysql.connect( 

host='127.0.0.1', 

user='root', 

password='root', 

db='scenic', 

port=3306, 

charset='utf8' 

) 

def get_scenic_data(self): 

"""获取数据 

""" 

cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor) 

sql = """ 

SELECT 

t.tourist_agency_name, 

rel.id_no, 

left(rel.id_no, 2) as province_code, 

DAYOFWEEK(gate.create_time) as 'non_weekend', 

CAST(SUBSTRING(rel.id_no, 7, 4) as UNSIGNED) 

FROM ticket_order_user_rel rel 

LEFT JOIN ticket_order t on t.id = rel.order_id 

LEFT JOIN order_user_gate_rel gate on gate.ticket_rel_id = rel.id 

WHERE t.tourist_agency_name != ''and t.pay_time !='' 

""" 

cursor.execute(sql) 

ret = cursor.fetchall() 

df = pd.DataFrame(ret) 

print(df.head) 

#数据清洗 

df['non_weekend'] = df['non_weekend'].apply(lambda x: 1 if x not in [1, 7] else 0) 

#新增有效标记列，即是否有id_no 

df['valid_id'] = df['id_no'].apply(lambda x: 1 if str(x).split !='' else 0) 

#计算比例 

df ['out_province_ratio'] = df['id_no'].apply( 

lambda x : 1 if(x['valid_id'] and x['province_code'] != '44') else 0 if x['valid_id'] else numpy.nan 

) 

df ['elderly_ratio'] = df.apply( 

lambda x: 1 if (x['valid_id'] and 2025 - int(x['age']) >= 60) else 0 if x['valid_id'] else numpy.nan 

#axis=1 

) 

print(df.head) 

#分组聚合计算占比 

result = df.groupby(['tourist_agency_name']).agg( 

total_visitors=('id_no', 'count'), 

valid_visitors=('valid_id', 'sum'), 

out_province=('out_province_ratio', 'sum'), 

elderly=('elderly_ratio', 'sum'), 

non_weekend_ratio=('non_weekend', 'mame') 

).reset_index() 

#计算实际比例 

result['out_province_retio'] = result['out_province'] / result['valid_visitors'].replace(0, numpy.nan) 

result['elderly_reatio'] = result['elderly'] / result['valid_visitors'].replace(0, numpy.nan) 

#清除中间列 

result = result.drop(['out_province', 'elderly']) 

result['out_province_retio'] = result['out_province_retio'].fillna(0) 

result['elderly_retio'] = result['elderly_retio'].fillna(0) 

#print(result.head) 

result.to_csv('scenic_data.csv') 

if __name__ == '__main__': 

mu = MysqlUtils() 

mu.get_scenic_data()